package com.education.ai.dao.impl;

import com.education.ai.dao.TeachingResourceDao;
import com.education.ai.entity.TeachingResource;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 教学资源数据访问实现类
 */
@Repository
public class TeachingResourceDaoImpl implements TeachingResourceDao {

    private static final Logger logger = LoggerFactory.getLogger(TeachingResourceDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Override
    public List<TeachingResource> findAll() {
        String sql = "SELECT * FROM teaching_resources";
        return mapResultsToResources(jdbcUtil.executeQuery(sql));
    }
    
    @Override
    public Optional<TeachingResource> findById(Long id) {
        String sql = "SELECT * FROM teaching_resources WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToResource(results.get(0)));
    }
    
    @Override
    public List<TeachingResource> findByType(String type) {
        String sql = "SELECT * FROM teaching_resources WHERE type = ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, type));
    }
    
    @Override
    public List<TeachingResource> findBySubject(String subject) {
        String sql = "SELECT * FROM teaching_resources WHERE subject = ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, subject));
    }
    
    @Override
    public List<TeachingResource> findBySubjectAndGrade(String subject, String grade) {
        String sql = "SELECT * FROM teaching_resources WHERE subject = ? AND grade = ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, subject, grade));
    }
    
    @Override
    public List<TeachingResource> findByTitleContaining(String titlePart) {
        String sql = "SELECT * FROM teaching_resources WHERE title LIKE ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, "%" + titlePart + "%"));
    }
    
    @Override
    public List<TeachingResource> findByKnowledgePoint(String knowledgePoint) {
        String sql = "SELECT * FROM teaching_resources WHERE knowledge_points LIKE ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, "%" + knowledgePoint + "%"));
    }
    
    @Override
    public List<TeachingResource> findByTag(String tag) {
        String sql = "SELECT * FROM teaching_resources WHERE tags LIKE ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, "%" + tag + "%"));
    }
    
    @Override
    public List<TeachingResource> findTopByUsageCount(int limit) {
        String sql = "SELECT * FROM teaching_resources ORDER BY usage_count DESC LIMIT ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, limit));
    }
    
    @Override
    public List<TeachingResource> findTopByRating(int limit) {
        String sql = "SELECT * FROM teaching_resources WHERE rating IS NOT NULL ORDER BY rating DESC LIMIT ?";
        return mapResultsToResources(jdbcUtil.executeQuery(sql, limit));
    }
    
    @Override
    public TeachingResource save(TeachingResource resource) {
        if (resource.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO teaching_resources (title, type, subject, grade, description, " +
                    "knowledge_points, resource_url, usage_count, rating, tags, create_time, update_time) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            // 设置初始值
            if (resource.getUsageCount() == null) {
                resource.setUsageCount(0);
            }
            
            Date now = new Date();
            if (resource.getCreateTime() == null) {
                resource.setCreateTime(now);
            }
            if (resource.getUpdateTime() == null) {
                resource.setUpdateTime(now);
            }
            
            long id = jdbcUtil.executeInsert(sql,
                    resource.getTitle(),
                    resource.getType(),
                    resource.getSubject(),
                    resource.getGrade(),
                    resource.getDescription(),
                    resource.getKnowledgePoints(),
                    resource.getResourceUrl(),
                    resource.getUsageCount(),
                    resource.getRating(),
                    resource.getTags(),
                    new Timestamp(resource.getCreateTime().getTime()),
                    new Timestamp(resource.getUpdateTime().getTime()));
            
            resource.setId(id);
            return resource;
        } else {
            // 更新操作
            update(resource);
            return resource;
        }
    }
    
    @Override
    public boolean update(TeachingResource resource) {
        String sql = "UPDATE teaching_resources SET title = ?, type = ?, subject = ?, grade = ?, " +
                "description = ?, knowledge_points = ?, resource_url = ?, usage_count = ?, rating = ?, " +
                "tags = ?, update_time = ? WHERE id = ?";
        
        // 更新时间
        resource.setUpdateTime(new Date());
        
        int rows = jdbcUtil.executeUpdate(sql,
                resource.getTitle(),
                resource.getType(),
                resource.getSubject(),
                resource.getGrade(),
                resource.getDescription(),
                resource.getKnowledgePoints(),
                resource.getResourceUrl(),
                resource.getUsageCount(),
                resource.getRating(),
                resource.getTags(),
                new Timestamp(resource.getUpdateTime().getTime()),
                resource.getId());
        
        return rows > 0;
    }
    
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM teaching_resources WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public boolean incrementUsageCount(Long id) {
        String sql = "UPDATE teaching_resources SET usage_count = usage_count + 1 WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public boolean updateRating(Long id, Float rating) {
        String sql = "UPDATE teaching_resources SET rating = ? WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, rating, id);
        return rows > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM teaching_resources";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为教学资源对象列表
     */
    private List<TeachingResource> mapResultsToResources(List<Map<String, Object>> results) {
        List<TeachingResource> resources = new ArrayList<>();
        for (Map<String, Object> row : results) {
            resources.add(mapRowToResource(row));
        }
        return resources;
    }
    
    /**
     * 将单行结果映射为教学资源对象
     */
    private TeachingResource mapRowToResource(Map<String, Object> row) {
        TeachingResource resource = new TeachingResource();
        resource.setId(((Number) row.get("id")).longValue());
        resource.setTitle((String) row.get("title"));
        resource.setType((String) row.get("type"));
        resource.setSubject((String) row.get("subject"));
        resource.setGrade((String) row.get("grade"));
        resource.setDescription((String) row.get("description"));
        resource.setKnowledgePoints((String) row.get("knowledge_points"));
        resource.setResourceUrl((String) row.get("resource_url"));
        
        if (row.get("usage_count") != null) {
            resource.setUsageCount(((Number) row.get("usage_count")).intValue());
        }
        
        if (row.get("rating") != null) {
            resource.setRating(((Number) row.get("rating")).floatValue());
        }
        
        resource.setTags((String) row.get("tags"));
        
        if (row.get("create_time") != null) {
            resource.setCreateTime((Date) row.get("create_time"));
        }
        
        if (row.get("update_time") != null) {
            resource.setUpdateTime((Date) row.get("update_time"));
        }
        
        return resource;
    }
} 